﻿using MesBSW.DAL.MesDB;
using MesBSW.DAL.MesDTO;
using MesBSW.DAL.RoleDB;
using MesBSW.Tools;
using MesBSW.View;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;

namespace MesBSW.BLL
{
    public class BaseControler
    {
        //初始化Sugar
        private SqlSugarClient Db = SqlSugares.GetInstance();


        public List<LanguageInfo> SearchAllLanguageInfo()
        {
            List<LanguageInfo> resList = new List<LanguageInfo>();
            using (MesDB ctx = new MesDB())
            {
                var query = ctx.LanguageInfos.AsQueryable();
                resList = query != null && query.Count() > 0 ? query.ToList<LanguageInfo>() : new List<LanguageInfo>();
            }
            return resList;
        }

        /// <summary>
        /// 查询服务器当前时间
        /// </summary>
        /// <returns></returns>
        public DateTime GetCurrDateTime()
        {
            using (MesDB ctx = new MesDB())
            {
                string tmpSql = "select getdate() as curr_date";
                var res = ctx.Database.SqlQuery<DateTime>(tmpSql);
                return res.FirstOrDefault();
            }
        }


        /// <summary>
        /// 查询MES系统中的所有物料分类
        /// </summary>
        /// <returns></returns>
        public List<MatGroup> SearchAllMatGroups()
        {
            List<MatGroup> resList = new List<MatGroup>();
            using (MesDB ctx = new MesDB())
            {
                var query = ctx.MatGroups.AsQueryable();
                resList = query != null && query.Count() > 0 ? query.ToList<MatGroup>() : new List<MatGroup>();
            }
            return resList;
        }

        //public static string getMAC()
        //{
        //    foreach (NetworkInterface nic in NetworkInterface.GetAllNetworkInterfaces())
        //    {
        //        if (nic.OperationalStatus == OperationalStatus.Up)
        //        {
        //            return GetMacAddress(nic);
        //        }
        //    }
        //    return "";
        //}

        ///// <summary>
        ///// 获取本机mac地址
        ///// </summary>
        ///// <param name="nic"></param>
        ///// <returns></returns>
        //public static string GetMacAddress(NetworkInterface nic)
        //{
        //    byte[] bytes = nic.GetPhysicalAddress().GetAddressBytes();
        //    string macAddress = BitConverter.ToString(bytes);
        //    return macAddress;
        //}

        /// <summary>
        /// 查询所有用户
        /// </summary>
        /// <returns></returns>
        public List<BaseUserModel> SearchAllUserInfo()
        {
            List<BaseUserModel> resList = new List<BaseUserModel>();
            using (RoleDB ctx = new RoleDB())
            {
                var query = ctx.base_user.AsQueryable();
                if (query != null && query.Count() > 0)
                {
                    resList.AddRange(query);
                }
            }
            return resList;
        }

        /// <summary>
        /// 查询角色的权限部门
        /// </summary>
        /// <returns></returns>
        public List<int> SelectUserDept(int id = 0)
        {
            List<int> deptIds = new List<int>();
            string sql = $@"SELECT
                *
            FROM
                base_dept
            WHERE
                id IN (
                    select * from(
                        SELECT value AS depts_id
                        FROM STRING_SPLIT((
                        SELECT depts_id_list AS depts_id_list 
                        FROM
                        (SELECT STRING_AGG(T2.depts_id, ',') WITHIN GROUP (ORDER BY T2.depts_id) AS depts_id_list
                        FROM base_user_role T1
                        LEFT JOIN base_role T2 ON T1.base_role_id = T2.id
                        where T1.base_user_id=1) AS subquery), ',')) a
                        where ISNULL(depts_id,'')<>''
                )";
            if (id != 0)
            {
                sql = $@"SELECT
                    *
                FROM
                    base_dept
                WHERE
                    id IN (
                        SELECT
                            value AS depts_id
                        FROM
                            STRING_SPLIT(
                                (
                                    SELECT
                                        LEFT(depts_id_list, LEN(depts_id_list) - 1) AS depts_id_list
                                    FROM
                                        (SELECT
                                            STRING_AGG(T2.depts_id, ',') WITHIN GROUP (ORDER BY T2.depts_id) AS depts_id_list
                                        FROM
                                            base_user_role T1
                                            LEFT JOIN base_role T2 ON T1.base_role_id = T2.id
                                        WHERE
                                            T1.base_user_id = {id}) AS subquery
                                ), ','
                            )
                    )";
            }
            var list = Db.Context.SqlQueryable<object>(sql).ToList();
            dynamic dylist = list;
            for (int i = 0; i < list.Count; i++)
            {
                deptIds.Add(dylist[i].id);
            }
            return deptIds;
        }

        /// <summary>
        /// 查询所有状态
        /// </summary>
        /// <returns></returns>
        public List<StatusType> GetAllStatus()
        {
            List<StatusType> typeList = new List<StatusType>();
            using (MesDB ctx = new MesDB())
            {
                var query = ctx.StatusTypes;
                if (query != null && query.Count() > 0)
                {
                    foreach (var item in query)
                    {
                        item.StatusList = new List<StatusInfo>();
                        var typeQuery = ctx.StatusInfos.Where(info => info.StypeID == item.ID);
                        if (typeQuery != null && typeQuery.Count() > 0)
                        {
                            item.StatusList.AddRange(typeQuery);
                        }
                        typeList.Add(item);
                    }
                }
            }
            return typeList;
        }

        /// <summary>
        /// 查询所有状态
        /// </summary>
        /// <returns></returns>
        public List<StatusInfo> SearchAllStatus()
        {
            using (MesDB ctx = new MesDB())
            {
                var query = ctx.StatusInfos;
                if (query != null && query.Count() > 0)
                {
                    return query.ToList();
                }
                else
                {
                    return new List<StatusInfo>();
                }
            }
        }


        ///// <summary>
        ///// 根据状态组别查询该组别以下的所有状态
        ///// </summary>
        ///// <param name="typeName">状态组别的名称</param>
        ///// <returns></returns>
        //public List<StatusInfo> GetStatusInfoByTypeName(string typeName)
        //{
        //    using (MesDB ctx = new MesDB())
        //    {
        //        var query = from a in ctx.StatusTypes
        //                    join b in ctx.StatusInfos on a.ID equals b.StypeID into res
        //                    from ab in res
        //                    where a.StypeName == typeName
        //                    select ab;
        //        if (query != null && query.Count() > 0)
        //        {
        //            return query.ToList();
        //        }
        //        else
        //        {
        //            return new List<StatusInfo>();
        //        }
        //    }
        //}

        /// <summary>
        /// 查询所有子站点信息
        /// </summary>
        /// <returns></returns>
        public List<SiteInfo> SearchAllSiteInfo()
        {
            using (MesDB ctx = new MesDB())
            {
                var query = ctx.SiteInfos.Where(s => s.IsFinal == 1).OrderBy(x=>x.SiteCode);
                if (query != null && query.Count() > 0)
                {
                    return query.ToList();
                }
                else
                {
                    return new List<SiteInfo>();
                }
            }
        }


        /// <summary>
        /// 查询所有站点信息
        /// </summary>
        /// <returns></returns>
        public List<SiteInfo> FindAllSiteInfo()
        {
            using (MesDB ctx = new MesDB())
            {
                var query = ctx.SiteInfos;
                if (query != null && query.Count() > 0)
                {
                    return query.ToList();
                }
                else
                {
                    return new List<SiteInfo>();
                }
            }
        }


        /// <summary>
        /// 查询所有站点组
        /// </summary>
        /// <returns></returns>
        public List<SiteGroupDTO> GetAllSiteGroup()
        {
            List<SiteGroupDTO> resList = new List<SiteGroupDTO>();
            using (MesDB ctx = new MesDB())
            {
                var query = from a in ctx.SiteGroups
                            join b in ctx.SiteInfoGroups on a.ID equals b.GroupID into res1
                            from ab in res1
                            join c in ctx.SiteInfos on ab.SiteID equals c.ID into res2
                            from bc in res2
                            select new { A = a, AB = ab, BC = bc };
                if (query != null && query.Count() > 0)
                {
                    var uniqueA = query.Select(x => x.A).Distinct();
                    if (uniqueA != null && uniqueA.Count() > 0)
                    {
                        foreach (var item in uniqueA)
                        {
                            SiteGroupDTO tmpBean = new SiteGroupDTO();
                            EntityTools.CopyEntity<SiteGroup, SiteGroupDTO>(item, ref tmpBean);
                            var tmpQuery = query.Where(x => x.A.ID == tmpBean.ID);
                            if (tmpQuery != null)
                            {
                                tmpBean.SiteInfoList = new List<SiteInfoGroupDTO>();
                                foreach (var tmpItem in tmpQuery)
                                {
                                    SiteInfoGroupDTO siteInfoGroupDTO = new SiteInfoGroupDTO();
                                    EntityTools.CopyEntity<SiteInfoGroup, SiteInfoGroupDTO>(tmpItem.AB, ref siteInfoGroupDTO);
                                    siteInfoGroupDTO.SiteInfo = tmpItem.BC;
                                    tmpBean.SiteInfoList.Add(siteInfoGroupDTO);
                                }
                            }

                            resList.Add(tmpBean);
                        }
                    }
                }
            }
            return resList;
        }


        ///// <summary>
        ///// 根据所有的维修原因
        ///// </summary>
        ///// <param name="siteID"></param>
        ///// <returns></returns>
        //public List<RepairReason> SearchAllRepairReason()
        //{
        //    List<RepairReason> resList = new List<RepairReason>();
        //    using (MesDB ctx = new MesDB())
        //    {
        //        var query = ctx.RepairReason;
        //        if (query != null && query.Count() > 0)
        //        {
        //            resList.AddRange(query);
        //        }
        //    }
        //    return resList;
        //}


        /// <summary>
        /// 新增更新日志
        /// </summary>
        /// <param name="tableName">资料表名</param>
        /// <param name="id">记录的ID</param>
        /// <param name="beforeData">更新前的值</param>
        /// <param name="afterData">更新后的值</param>
        /// <param name="changeType">类型</param>
        /// <param name="changeUser">操作者</param>
        /// <param name="changeDate">更新日期</param>
        /// <returns></returns>
        public MesLog InitMesLog(string tableName, int id, string beforeData, string afterData, string changeType, string changeUser, DateTime? changeDate = null)
        {
            MesLog resLog = new MesLog();
            resLog.ID = 0;
            resLog.TableName = tableName;
            resLog.TableID = id;
            resLog.BeforeData = beforeData;
            resLog.AfterData = afterData;
            resLog.ChangeType = changeType;
            resLog.ChangeUser = changeUser;
            if (changeDate.HasValue)
            {
                resLog.ChangeDate = changeDate.Value;
            }
            else
            {
                resLog.ChangeDate = GetCurrDateTime();
            }
            return resLog;
        }

        /// <summary>
        /// 查询所有产线的设定。
        /// </summary>
        /// <returns></returns>
        public List<ProdLineSetting> SearchAllProdLineSetting()
        {
            List<ProdLineSetting> resList = new List<ProdLineSetting>();
            using (MesDB ctx = new MesDB())
            {
                var query = ctx.ProdLineSettings;
                if (query != null && query.Count() > 0)
                {
                    resList.AddRange(query);
                }
            }
            return resList;
        }


        /////// <summary>
        /////// 查询所有站点流程明细
        /////// </summary>
        /////// <returns></returns>
        ////public List<Tuple<SiteGroup, SiteInfoGroup, SiteInfo>> SearchAllSiteGroup()
        ////{
        ////    List<Tuple<SiteGroup, SiteInfoGroup, SiteInfo>> resList = new System.Collections.Generic.List<Tuple<SiteGroup, SiteInfoGroup, SiteInfo>>();
        ////    using (MesDB ctx = new MesDB())
        ////    {
        ////        var query = from a in ctx.SiteGroups
        ////                    join b in ctx.SiteInfoGroups on a.ID equals b.GroupID into res1
        ////                    from ab in res1
        ////                    join c in ctx.SiteInfos on ab.SiteID equals c.ID into res2
        ////                    from bc in res2
        ////                    select new { A = a, AB = ab, BC = bc };
        ////        if (query != null && query.Count() > 0)
        ////        {
        ////            foreach (var item in query)
        ////            {
        ////                resList.Add(new Tuple<SiteGroup, SiteInfoGroup, SiteInfo>(item.A, item.AB, item.BC));
        ////            }
        ////        }
        ////    }
        ////    return resList;
        ////}

        //public List<MaterialTypeList> SearchMaterialTypeForSMTPoint()
        //{
        //    List<MaterialTypeList> resList = new List<MaterialTypeList>();
        //    using (MesDB ctx = new MesDB())
        //    {
        //        var query = ctx.MaterialTypeList;
        //        if (query != null && query.Count() > 0)
        //        {
        //            resList.AddRange(query);
        //        }
        //    }
        //    return resList;

        //}


        //public List<AttSchedule> SearchAttSchedule()
        //{
        //    List<AttSchedule> resList = new List<AttSchedule>();
        //    using (MesDB ctx = new MesDB())
        //    {
        //        var query = ctx.AttSchedules.AsQueryable();
        //        if (query != null && query.Count() > 0)
        //        {
        //            resList.AddRange(query);
        //        }
        //    }
        //    return resList;
        //}


        //public List<SiteGroupDTO> SearchAllSiteGroup()
        //{
        //    List<SiteGroupDTO> resList = new List<SiteGroupDTO>();
        //    using (MesDB ctx = new MesDB())
        //    {
        //        var query = from a in ctx.SiteGroups
        //                    join b in ctx.SiteInfoGroups on a.ID equals b.GroupID into res1
        //                    from ab in res1
        //                    join c in ctx.SiteInfos on ab.SiteID equals c.ID into res2
        //                    from bc in res2
        //                    select new { A = a, AB = ab, BC = bc };
        //        if (query != null && query.Count() > 0)
        //        {
        //            var tmpList = query.ToList();
        //            foreach (var group in tmpList.Select(q => q.A).Distinct().ToList<SiteGroup>())
        //            {
        //                SiteGroupDTO groupDto = new SiteGroupDTO();
        //                EntityTools.CopyEntity<SiteGroup, SiteGroupDTO>(group, ref groupDto);

        //                var siteQuery = tmpList.Where(q => q.A.ID == group.ID).OrderBy(q => q.AB.Sort);
        //                if (siteQuery != null && siteQuery.Count() > 0)
        //                {
        //                    groupDto.SiteInfoList = new List<SiteInfoGroupDTO>();
        //                    foreach (var item in siteQuery)
        //                    {
        //                        SiteInfoGroupDTO midBean = new SiteInfoGroupDTO();
        //                        EntityTools.CopyEntity<SiteInfoGroup, SiteInfoGroupDTO>(item.AB, ref midBean);
        //                        midBean.SiteInfo = item.BC;
        //                        groupDto.SiteInfoList.Add(midBean);
        //                    }
        //                }
        //                resList.Add(groupDto);
        //            }
        //        }
        //    }
        //    return resList;
        //}

        //public List<EmplInfo> SearchAllEmplInfo()
        //{
        //    List<EmplInfo> resList = new List<EmplInfo>();
        //    using (MesDB ctx = new MesDB())
        //    {
        //        var query = ctx.EmplInfos.AsQueryable();
        //        resList = query != null && query.Count() > 0 ? query.ToList<EmplInfo>() : null;
        //    }
        //    return resList;
        //}
        ///// <summary>
        ///// 查询角色的权限部门
        ///// </summary>
        ///// <returns></returns>
        //public List<int> SelectUserDept(int id = 0)
        //{
        //    List<int> deptIds = new List<int>();
        //    string sql = $@"SELECT
        //        *
        //    FROM
        //        base_dept
        //    WHERE
        //        id IN (
        //            SELECT
        //                value AS depts_id
        //            FROM
        //                STRING_SPLIT(
        //                    (
        //                        SELECT
        //                            LEFT(depts_id_list, LEN(depts_id_list) - 1) AS depts_id_list
        //                        FROM
        //                            (SELECT
        //                                STRING_AGG(T2.depts_id, ',') WITHIN GROUP (ORDER BY T2.depts_id) AS depts_id_list
        //                            FROM
        //                                base_user_role T1
        //                                LEFT JOIN base_role T2 ON T1.base_role_id = T2.id
        //                            WHERE
        //                                T1.base_user_id = {MainFrm.LoginUser.id}) AS subquery
        //                    ), ','
        //                )
        //        )";
        //    if (id != 0)
        //    {
        //        sql = $@"SELECT
        //            *
        //        FROM
        //            base_dept
        //        WHERE
        //            id IN (
        //                SELECT
        //                    value AS depts_id
        //                FROM
        //                    STRING_SPLIT(
        //                        (
        //                            SELECT
        //                                LEFT(depts_id_list, LEN(depts_id_list) - 1) AS depts_id_list
        //                            FROM
        //                                (SELECT
        //                                    STRING_AGG(T2.depts_id, ',') WITHIN GROUP (ORDER BY T2.depts_id) AS depts_id_list
        //                                FROM
        //                                    base_user_role T1
        //                                    LEFT JOIN base_role T2 ON T1.base_role_id = T2.id
        //                                WHERE
        //                                    T1.base_user_id = {id}) AS subquery
        //                        ), ','
        //                    )
        //            )";
        //    }
        //    var list = Db.Context.SqlQueryable<object>(sql).ToList();
        //    dynamic dylist = list;
        //    for (int i = 0; i < list.Count; i++)
        //    {
        //        deptIds.Add(dylist[i].id);
        //    }
        //    return deptIds;
        //}
    }
}
